﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Reflection;

namespace MossmanMill.DataModel
{
    /// <summary>
    /// Database Access (DAO Gateway) class
    /// </summary>
    /// <dates>
    /// 12/04/2012 - created by Luke Atkins
    /// </dates>
    public class DatabaseAccess : IDatabaseAccess
    {
        //Fixed Connection String, the path to the .mdf file is now relative, so in this case, the database is located alongside the executable.
        private const string CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataModel\HoneywellRacks.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        
        private static DatabaseAccess instance;

        public static DatabaseAccess Instance
        {
            get
            {
                if (instance == null) instance = new DatabaseAccess();
                return instance;
            }
        }

        private SqlConnection connection;

        private DatabaseAccess()
        {
            connection = new SqlConnection(CONNECTION_STRING);
        }

        /// <summary>
        /// Get a list of Room Locations (these rooms hold racks)
        /// </summary>
        /// <returns>A list of Rooms</returns>
        public List<Room> GetRooms()
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Room", connection);
            var reader = command.ExecuteReader();
            var rooms = new List<Room>();
            while (reader.Read())
            {
                rooms.Add(new Room((int)reader["Room_Id"], reader["Room_Name"].ToString(), reader["Room_Location"].ToString()));
            }
            Close();
            return rooms;
        }

        /// <summary>
        /// Get a room by it's Id
        /// </summary>
        /// <param name="id">Room Id</param>
        /// <returns>The Room associated with the Id, or null if it doesn't exist</returns>
        public Room GetRoom(int id)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Room WHERE Room_Id=" + id, connection);
            var reader = command.ExecuteReader();
            Room room = null;
            while (reader.Read())
            {
                room = new Room((int)reader["Room_Id"], reader["Room_Name"].ToString(), reader["Room_Location"].ToString());
            }
            Close();
            return room;
        }

        public Room GetRoom(string name)
        {
            Room room = null;
            try
            {
                Open();
                var command = new SqlCommand(String.Format("Select * FROM Room Where Room_Name='{0}'", name.Normalize()), connection);
                var reader = command.ExecuteReader();
                if (reader.Read()) room = new Room((int)reader["Room_Id"], reader["Room_Name"].ToString(), reader["Room_Location"].ToString());
                reader.Close();
                Close();

            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            return room;
        }

        /// <summary>
        /// Add a new Room to the database
        /// </summary>
        /// <param name="name">Room name</param>
        /// <param name="location">A 2D point in string format: "x,y"</param>
        /// <returns>True if successful</returns>
        public bool AddRoom(string name, string location)
        {
            try
            {
                if (GetRoom(name) != null) return false; //Avoid constraint conflict
                Open();
                var sql = String.Format("INSERT INTO Room VALUES ('{0}', '{1}')", name.Normalize(), location.Normalize());
                var command = new SqlCommand(sql, connection);
                var result = command.ExecuteNonQuery() > 0;
                Close();
                return result;
            }
            catch (System.Data.SqlClient.SqlException e) 
            {   // Handle unique constraint violation 
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
                return false;
            }
        }

        public void UpdateRoom(Room room)
        {
            Open();
            var sql = String.Format("UPDATE Room SET Room_Name='{0}', Room_Location='{1}' WHERE Room_Id={2}", room.Name, room.LocString(), room.Id);
            var command = new SqlCommand(sql, connection);
            command.ExecuteNonQuery();
            Close();
        }

        /// <summary>
        /// Add a new Rack to the database
        /// </summary>
        /// <param name="id">Room Id</param>
        /// <param name="model">Rack Model Name</param>
        /// <param name="address">Rack Address String eg "CPM0101"</param>
        /// <returns>True if successful</returns>
        public bool AddRack(int id, string model, string address)
        {
            try
            {
                Open();
                var sql = String.Format("INSERT INTO Rack VALUES ({0}, '{1}', '{2}', null)", id, model.Normalize(), address.Normalize());
                var command = new SqlCommand(sql, connection);
                var result = command.ExecuteNonQuery() > 0;
                Close();
                return result;

            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
                return false;
            }
        }

        /// <summary>
        /// Get a list of all racks
        /// </summary>
        /// <returns>List of racks</returns>
        public List<Rack> GetRacks()
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Rack", connection);
            var reader = command.ExecuteReader();
            var racks = new List<Rack>();
            while (reader.Read())
            {
                racks.Add(new Rack((int)reader["Rack_Id"], (int)reader["Room_Id"], reader["Rack_Model"].ToString(), reader["Rack_Address"].ToString()));
            }
            Close();
            return racks;
        }

        public Rack GetRack(int rackId)
        {
            if (connection.State == System.Data.ConnectionState.Open) Close();
            Open();
            var command = new SqlCommand("SELECT * FROM Rack WHERE Rack_Id="+rackId, connection);
            var reader = command.ExecuteReader();
            Rack rack = null;
            if (reader.Read())
            {
                rack = new Rack((int)reader["Rack_Id"], (int)reader["Room_Id"], reader["Rack_Model"].ToString(), reader["Rack_Address"].ToString());
            }
            Close();
            return rack;
        }

        public List<Rack> GetRacks(int room)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Rack WHERE Room_Id=" + room, connection);
            var reader = command.ExecuteReader();
            var racks = new List<Rack>();
            while (reader.Read())
            {
                racks.Add(new Rack((int)reader["Rack_Id"], (int)reader["Room_Id"], reader["Rack_Model"].ToString(), reader["Rack_Address"].ToString()));
            }
            Close();
            return racks;
        }

        /// <summary>
        /// Get the slottable in a certain slot on a rack
        /// </summary>
        /// <param name="rack">Rack Id</param>
        /// <param name="slot">Slot Position</param>
        /// <param name="look1">Looking for Processor</param>
        /// <param name="look2">Looking for Processor part 2</param>
        /// <returns>A slottable</returns>
        public Slottable GetSlottable(int rack, int slot, bool look1 = false, bool look2 = false)
        {
            try
            {
                Open();
                var sql = String.Format("SELECT * FROM Slottable WHERE Rack_Id={0} AND Slot_Position={1}", rack, slot);
                var command = new SqlCommand(sql, connection);
                var reader = command.ExecuteReader();
                if (!reader.Read())
                {
                    Close();
                    if (look1) return GetSlottable(rack, slot - 1, false, true);
                    return null;
                }
                var objs = new object[reader.FieldCount];
                reader.GetValues(objs);
                foreach (var obj in objs) Console.WriteLine(obj);
                var rackId = (int)reader["Rack_Id"];
                var slotId = (int)reader["Slot_Id"];
                var pos = (int)reader["Slot_Position"];
                var table = "Card";
                var type = reader["Slot_Type"].ToString();
                if (look2 && !type.Equals("P"))
                {
                    Close();
                    return null;
                }
                switch (type)
                {
                    case "N":
                        table = "NetworkCard";
                        break;
                    case "P":
                        table = "Processor";
                        break;
                }
                sql = String.Format("SELECT * FROM {0} WHERE Slot_Id={1}", table, slotId);
                command = new SqlCommand(sql, connection);
                reader.Close();
                reader = command.ExecuteReader();
                if (!reader.Read())
                {
                    Close();
                    return null;
                }
                switch (type)
                {
                    case "P":
                        var p = new Processor((int)reader["Processor_Id"], rackId, reader["Processor_Model"].ToString(), pos);
                        Close();
                        return p;
                    case "N":
                        var nc = new NetworkCard((int)reader["Network_Id"], rackId, reader["Network_Model"].ToString(), reader["Network_Type"].ToString()[0], slot);
                        Close();
                        return nc;
                    case "C":
                        var cardId = (int)reader["Card_Id"];
                        var modId = (int)reader["Module_Id"];
                        sql = String.Format("SELECT * FROM IOModule WHERE Module_Id={0}", modId);
                        reader.Close();
                        command = new SqlCommand(sql, connection);
                        reader = command.ExecuteReader();
                        if (!reader.Read())
                        {
                            Close();
                            return null;
                        }
                        var c = new Card(slotId, cardId, rackId, (int)reader["Module_Type"], reader["Module_Name"].ToString(), reader["Module_Description"].ToString(), (int)reader["Module_Channels"], modId, pos);
                        Close();
                        return c;
                }
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            Close();
            return null;
        }

        /// <summary>
        /// Get a list of slottables
        /// </summary>
        /// <param name="rack">Rack to get slottables from. If null, it will get all slottables</param>
        /// <returns>A List of Slottables</returns>
        public List<Slottable> GetSlottables(Rack rack = null)
        {
            Open();
            var where = rack == null ? "" : " WHERE Rack_Id=" + rack.Id;
            var command = new SqlCommand("SELECT * FROM Slottable" + where, connection);
            var reader = command.ExecuteReader();
            var slottables = new List<Slottable>();
            var data = new List<object[]>();
            while (reader.Read())
            {
                object[] d = new object[4];
                d[0] = (int)reader["Slot_Id"];
                d[1] = (int)reader["Rack_Id"];
                d[2] = reader["Slot_Type"].ToString()[0];
                d[3] = (int)reader["Slot_Position"];
                data.Add(d);
            }
            reader.Close();
            Close();
            foreach (var d in data)
            {
                var slotId = (int)d[0];
                var rackId = (int)d[1];
                char type = (char)d[2];
                var position = (int)d[3];
                switch (type)
                {
                    case 'N':
                        var network = GetNetworkCard(slotId, rackId);
                        network.RackId = rackId;
                        network.Position = position;
                        network.SlotType = type;
                        slottables.Add(network);
                        break;
                    case 'P':
                        var processor = GetProcessor(slotId, rackId, position);
                        processor.RackId = rackId;
                        processor.Position = position;
                        processor.SlotType = type;
                        slottables.Add(processor);
                        break;
                    case 'C':
                        var card = GetCard(slotId,rackId, position);
                        if (card == null) break;
                        card.RackId = rackId;
                        card.Position = position;
                        card.SlotType = type;
                        slottables.Add(card);
                        break;
                }
            }
            return slottables;
        }

        /// <summary>
        /// Get all Sensors
        /// </summary>
        /// <returns>A list of sensors</returns>
        public List<Sensor> GetSensors(Card card = null)
        {
            Open();
            String where = card == null ? "" : " Where Card_Id=" + card.CardId;
            var command = new SqlCommand("SELECT * FROM Sensor" + where, connection);
            var reader = command.ExecuteReader();
            var sensors = new List<Sensor>();
            while (reader.Read())
            {
                sensors.Add(new Sensor((int)reader["Sensor_Id"], reader["Sensor_Tag"].ToString(), reader["Sensor_Param"].ToString(), reader["Sensor_Desc"].ToString(), (int)reader["Sensor_Channel"], (int)reader["Card_Id"]));
            }
            Close();
            return sensors;
        }

        /// <summary>
        /// Get cards that can be used. (The inventory of IOModule cards)
        /// </summary>
        /// <returns>List of Cards with no Card/Slot Ids</returns>
        public List<Card> GetAvailableCards()
        {
            Open();
            var command = new SqlCommand("SELECT * FROM IOModule", connection);
            var reader = command.ExecuteReader();
            var cards = new List<Card>();
            while (reader.Read())
            {
                cards.Add(new Card(-1, -1, -1, (int)reader["Module_Type"], reader["Module_Name"].ToString(), reader["Module_Description"].ToString(), (int)reader["Module_Channels"], (int)reader["Module_Id"], -1));
            }
            Close();
            return cards;
        }

        /// <summary>
        /// Get a card associated with slottable
        /// </summary>
        /// <param name="slotId">Slottable Id</param>
        /// <returns>Card</returns>
        public Card GetCard(int slotId, int rackId, int pos)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Card WHERE Slot_Id=" + slotId, connection);
            var cardReader = command.ExecuteReader();
            Card card = null;
            if (cardReader.Read())
            {
                var modId = (int)cardReader["Module_Id"];
                var cardId = (int)cardReader["Card_Id"];                
                cardReader.Close();
                var command2 = new SqlCommand("SELECT * FROM IOModule WHERE Module_Id=" + modId, connection);
                var moduleReader = command2.ExecuteReader();
                if (moduleReader.Read())
                {
                    card = new Card(slotId, cardId, rackId, (int)moduleReader["Module_Type"], moduleReader["Module_Name"].ToString(), moduleReader["Module_Description"].ToString(), (int)moduleReader["Module_Channels"], modId, pos);
                }
            }
            Close();
            return card;
        }

        public Card GetCard(int cardId)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Card WHERE Card_Id=" + cardId, connection);
            var reader = command.ExecuteReader();
            int slotId, modId, rackId, pos;
            if (reader.Read())
            {
                slotId = (int)reader["Slot_Id"];
                modId = (int)reader["Module_Id"];
                reader.Close();

                command = new SqlCommand("SELECT * FROM Slottable WHERE Slot_Id=" + slotId, connection);
                reader = command.ExecuteReader();
                reader.Read();
                rackId = (int)reader["Rack_Id"];
                pos = (int)reader["Slot_Position"];
                reader.Close();

                command = new SqlCommand("SELECT * FROM IOModule WHERE Module_Id=" + modId, connection);
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    var card = new Card(slotId, cardId, rackId, (int)reader["Module_Type"], reader["Module_Name"].ToString(), reader["Module_Description"].ToString(), (int)reader["Module_Channels"], modId, pos);
                    reader.Close();
                    return card;
                }
            }
            Close();
            return null;

        }

        /// <summary>
        /// Get a network card associated with slottable
        /// </summary>
        /// <param name="slotId">Slottable Id</param>
        /// <returns>Network Card</returns>
        private NetworkCard GetNetworkCard(int slotId, int rackId)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM NetworkCard WHERE Slot_Id=" + slotId, connection);
            var reader = command.ExecuteReader();
            NetworkCard card = null;
            if (reader.Read())
            {
                var id = (int)reader["Network_Id"];
                var model = reader["Network_Model"].ToString();
                var type = reader["Network_Type"].ToString()[0];
                reader.Close();
                card = new NetworkCard(id, rackId, model, type, GetSlotForSlottable(slotId));
                card.SlotId = slotId;
            }
            Close();
            return card;
        }

        /// <summary>
        /// Get slot position for a Slottable
        /// </summary>
        /// <param name="id">Slottable Id</param>
        /// <returns>Slot position</returns>
        private int GetSlotForSlottable(int id)
        {
            var command = new SqlCommand("SELECT Slot_Position FROM Slottable WHERE Slot_Id=" + id, connection);
            return (int)command.ExecuteScalar();
        }

        /// <summary>
        /// Get a processor associated with slottable
        /// </summary>
        /// <param name="slotId">Slottable Id</param>
        /// <returns>Processor</returns>
        private Processor GetProcessor(int slotId, int rackId, int pos)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Processor WHERE Slot_Id=" + slotId, connection);
            var reader = command.ExecuteReader();
            Processor card = null;
            if (reader.Read())
            {
                card = new Processor((int)reader["Processor_Id"], rackId, reader["Processor_Model"].ToString(), pos);
                card.SlotId = slotId;
            }
            Close();
            return card;
        }

        public Sensor GetSensor(int card, int channel)
        {
            try
            {
                Open();
                var sql = String.Format("SELECT * FROM Sensor WHERE Card_Id={0} AND Sensor_Channel={1}", card, channel);
                var command = new SqlCommand(sql, connection);
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    var sensor = new Sensor((int)reader["Sensor_Id"], reader["Sensor_Tag"].ToString(), reader["Sensor_Param"].ToString(), reader["Sensor_Desc"].ToString(), channel, card);
                    Close();
                    return sensor;
                }
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            return null;
        }

        /// <summary>
        /// Get a list of distinct Rack models
        /// </summary>
        /// <returns></returns>
        public List<string> GetRackModels()
        {
            Open();
            var sql = new SqlCommand("SELECT DISTINCT Rack_Model FROM RACK", connection);
            var reader = sql.ExecuteReader();
            var models = new List<string>();
            while (reader.Read())
            {
                models.Add(reader["Rack_Model"].ToString());
            }
            Close();
            return models;
        }

        /// <summary>
        /// Get a list of distict Network Card Model
        /// </summary>
        /// <returns></returns>
        public List<string> GetNetworkCardModels()
        {
            Open();
            var sql = new SqlCommand("SELECT DISTINCT Network_Model FROM NetworkCard", connection);
            var reader = sql.ExecuteReader();
            var models = new List<string>();
            while (reader.Read())
            {
                models.Add(reader["Network_Model"].ToString());
            }
            Close();
            return models;
        }

        /// <summary>
        /// Get a list of distinct Processor Models
        /// </summary>
        /// <returns></returns>
        public List<string> GetProcessorModels()
        {
            Open();
            var sql = new SqlCommand("SELECT DISTINCT Processor_Model FROM Processor", connection);
            var reader = sql.ExecuteReader();
            var models = new List<string>();
            while (reader.Read())
            {
                models.Add(reader["Processor_Model"].ToString());
            }
            Close();
            return models;
        }

        public void DeleteRack(Rack rack)
        {
            Open();
            foreach (var card in rack.Cards)
            {
                if (card == null) continue;
                DeleteSlottable(rack.Id, card.Position);
                Open();
            }
            var sql = new SqlCommand("DELETE FROM Rack Where Rack_Id=" + rack.Id, connection);
            sql.ExecuteNonQuery();
            Close();
        }

        public Rack AddRack(Rack rack)
        {
            Rack stored = null;
            try
            {
                Open();
                var command = new SqlCommand(String.Format("INSERT INTO Rack VALUES ({0}, '{1}', '{2}', NULL)", rack.Room.Id, rack.Model, rack.Address), connection);
                Open();
                command.ExecuteNonQuery();
                command = new SqlCommand("SELECT MAX(Rack_Id) FROM Rack", connection);
                rack.SetId((int)command.ExecuteScalar());
                foreach (Slottable card in rack.Cards)
                {
                    if (card == null) continue;
                    card.RackId = rack.Id;
                    switch (card.SlotType)
                    {
                        case 'P':
                            AddProcessor(rack.Id, card.Position, ((Processor)card).Model);
                            break;
                        case 'N':
                            var nCard = ((NetworkCard)card);
                            AddNetworkCard(rack.Id, card.Position, nCard.Model, nCard.Type, nCard.Connection);
                            break;
                        case 'C':
                            var cCard = ((Card)card);
                            AddCard(rack.Id, card.Position, cCard.ModuleId);
                            Open();
                            command = new SqlCommand("SELECT MAX(Card_Id) From Card", connection);
                            cCard.SetCardId((int)command.ExecuteScalar());
                            foreach (Sensor sensor in cCard.Sensors)
                            {
                                if (sensor == null) continue;
                                DeleteSensor(cCard.SlotId, sensor.Channel);
                                Open();
                                AddSensor(new Sensor(-1, sensor.Tag, sensor.Param, sensor.Desc, sensor.Channel, cCard.CardId));
                            }
                            break;
                    }
                }
                Open();
                command = new SqlCommand("SELECT MAX(Rack_Id) FROM Rack", connection);
                stored = GetRack((int)command.ExecuteScalar());

            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                Console.WriteLine("Failed to Add Rack");
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
                Close();
            }
            Close();
            return stored;
        }

        public void UpdateRack(Rack rack)
        {
            try
            {
                Open();
                var oldRack = GetRack(rack.Id);
                Open();
                for (int i = 0; i < 13; i++)
                {
                    if (oldRack.Cards[i] == null && rack.Cards[i] != null) //If there is a new card where there was no card
                    {
                        var card = rack.Cards[i];
                        Open();
                        switch (card.SlotType)
                        {
                            case 'P':
                                AddProcessor(rack.Id, card.Position, ((Processor)card).Model);
                                break;
                            case 'N':
                                var nCard = ((NetworkCard)card);
                                AddNetworkCard(rack.Id, card.Position, nCard.Model, nCard.Type, nCard.Connection);
                                break;
                            case 'C':
                                var cCard = ((Card)card);
                                AddCard(rack.Id, card.Position, cCard.ModuleId);
                                var command = new SqlCommand("SELECT MAX(Card_Id) From Card", connection);
                                cCard.SetId((int)command.ExecuteScalar());
                                foreach (Sensor sensor in cCard.Sensors)
                                {
                                    if (sensor == null) continue;
                                    Open();
                                    AddSensor(new Sensor(-1, sensor.Tag, sensor.Param, sensor.Desc, sensor.Channel, cCard.CardId));
                                }
                                break;
                        }
                    }
                    if (oldRack.Cards[i] != null) //If A card exists already
                    {
                        if (rack.Cards[i] == null) //And there is no card in the new rack
                        {
                            if (oldRack.Cards[i].SlotType == 'C')
                            {
                                foreach (var sensor in ((Card)oldRack.Cards[i]).Sensors)
                                {
                                    if (sensor != null)
                                    {
                                        Open();
                                        DeleteSensor(sensor.CardId, sensor.Channel);
                                    }
                                }
                            }
                            Open();
                            DeleteSlottable(oldRack.Id, i);
                            Open();
                        }
                        else //There is a new card, so update it
                        {
                            var card = rack.Cards[i];
                            Open();
                            switch (card.SlotType)
                            {
                                case 'P':
                                    var pCard = (Processor)card;
                                    var command = new SqlCommand(String.Format("UPDATE Processor SET Processor_Model='{0}', WHERE Processor_Id={1}", pCard.Model, pCard.SlotId), connection);
                                    command.ExecuteNonQuery();
                                    break;
                                case 'N':
                                    var nCard = (NetworkCard)card;
                                    //May have DB Constraint issue
                                    command = new SqlCommand(String.Format("UPDATE NetworkCard SET Network_Model='{0}', Network_Type='{1}', Network_Connection={2} WHERE Network_Id={3}", nCard.Model, nCard.Type, nCard.Connection, nCard.SlotId), connection);
                                    command.ExecuteNonQuery();
                                    break;
                                case 'C':
                                    //Due to constraints Cards need to be wiped and rebuilt :/
                                    var cCard = (Card)card;
                                    Open();
                                    var sensors = cCard.Sensors; //Hold onto sensors

                                    Open();
                                    var oldCard = (Card)oldRack.Cards[i];
                                    Open();
                                    foreach (var sensor in oldCard.Sensors) { //Delete old sensors
                                        if (sensor == null) continue;
                                        Open();
                                        DeleteSensor(sensor.CardId, sensor.Channel); 
                                    }
                                    Open();
                                    AddCard(rack.Id, cCard.Position, cCard.ModuleId);
                                    Open();
                                    command = new SqlCommand("SELECT MAX(Card_Id) From Card", connection);
                                    cCard.SetId((int)command.ExecuteScalar());
                                    foreach (var sensor in sensors) //Add new/updated sensors
                                    {
                                        if (sensor == null) continue;
                                        Open();
                                        AddSensor(new Sensor(-1, sensor.Tag, sensor.Param, sensor.Desc, sensor.Channel, cCard.CardId));
                                    }
                                    break;
                            }
                        }
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                Console.WriteLine("Failed to Update Rack");
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            Close();
        }

        private void DeleteSensor(int cardId, int channel)
        {
            try
            {
                Open();
                var sql = new SqlCommand(String.Format("DELETE FROM Sensor WHERE Card_Id={0} AND Sensor_Channel={1}", cardId, channel), connection);
                sql.ExecuteNonQuery();
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// Add a new Network Card
        /// </summary>
        /// <param name="rack">Rack Id</param>
        /// <param name="position">Slot Position</param>
        /// <param name="model">Network Card Model</param>
        /// <param name="type">'U'/'D' Upstream (connections back to server or another Rack)</param>
        /// <param name="conn">Other NetworkCard Id (I want to change this to other Rack Id) </param>
        public void AddNetworkCard(int rack, int position, string model, char type, int conn)
        {

            try{
                Open();
                var sql = String.Format("INSERT INTO Slottable VALUES ({0}, 'N', {1})", rack, position);
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                command = new SqlCommand("SELECT MAX(Slot_Id) FROM Slottable", connection);
                int slotId = (int)command.ExecuteScalar();
                sql = String.Format("INSERT INTO NetworkCard VALUES ({0},'{1}','{2}',{3})", slotId, model, type, conn);
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// Add a new Processor
        /// </summary>
        /// <param name="rack">Rack Id</param>
        /// <param name="position">Slot Position</param>
        /// <param name="model">Model Name</param>
        public void AddProcessor(int rack, int position, string model)
        {
            try{
                Open();
                var sql = String.Format("INSERT INTO Slottable VALUES ({0}, 'P', {1})", rack, position);
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                command = new SqlCommand("SELECT MAX(Slot_Id) FROM Slottable", connection);
                int slotId = (int)command.ExecuteScalar();
                sql = String.Format("INSERT INTO Processor VALUES ({0},'{1}')", slotId, model);
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        
        }

        public Card GetIOModule(string model, int channels)
        {
            Card card = null;
            try
            {
                Open();
                var sql = new SqlCommand(String.Format("SELECT * FROM IOModule WHERE Module_Name='{0}' AND Module_Channels={1}", model.Normalize(), channels), connection);
                var reader = sql.ExecuteReader();
                if (reader.Read()) card = new Card(-1, -1, -1, (int)reader["Module_Type"], reader["Module_Name"].ToString(), reader["Module_Description"].ToString(), (int)reader["Module_Channels"], -1, -1);
                reader.Close();
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            return card;
        }

        /// <summary>
        /// Add new IOModule
        /// </summary>
        /// <param name="model">Model Name</param>
        /// <param name="type">DO,DI,AO,AI,OTHER</param>
        /// <param name="channels">Number of channels</param>
        /// <param name="desc">Description</param>
        public void AddIOModule(string model, int type, int channels, string desc)
        {
            try{
                if (GetIOModule(model, channels) != null) return;
                Open();
                var sql = String.Format("INSERT INTO IOModule VALUES ('{0}',{1},{2},'{3}')", model.Normalize(), type, channels, desc.Normalize());
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// Add new Card
        /// </summary>
        /// <param name="rack">Rack Id</param>
        /// <param name="position">Slot Position</param>
        /// <param name="module">Module Id</param>
        public void AddCard(int rack, int position, int module)
        {

            try{
                Open();
                DeleteSlottable(rack, position);
                Open();
                var command = new SqlCommand(String.Format("INSERT INTO Slottable VALUES ({0}, 'C', {1})", rack, position), connection);
                command.ExecuteNonQuery();
                command = new SqlCommand("SELECT MAX(Slot_Id) FROM Slottable", connection);
                int slotId = (int)command.ExecuteScalar();
                command = new SqlCommand(String.Format("INSERT INTO Card VALUES ({0},'{1}')", slotId, module), connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        public void AddSensor(Sensor sensor)
        {
            if (sensor.IsBlank()) return; //Reject Blank Sensors from the importer.
            string sql = null;
            try{
                sql = String.Format("INSERT INTO Sensor VALUES ({0},{1},'{2}','{3}','{4}')", sensor.CardId, sensor.Channel, sensor.Tag, sensor.Param, sensor.Desc);
                Open();
                //TODO Fix Constraint Issue
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.Data.SqlClient.SqlException)
            {   // Handle unique constraint violation
                Console.WriteLine(sql);
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }

        }

        public void updateSensor(int sensor, string tag, string param, string desc)
        {
            try
            {
                Open();
                var sql = String.Format("UPDATE Sensor SET Sensor_Tag='{0}', Sensor_Param='{1}', Sensor_Desc='{2}' WHERE Sensor_Id={3}",tag,param,desc, sensor);
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// Swap a card with another module
        /// </summary>
        /// <param name="card">Card Id</param>
        /// <param name="module">New Module Id</param>
        public void ChangeCard(int card, int module)
        {
            try 
            {
                Open();
                var sql = String.Format("UPDATE Card SET Module_Id={0} WHERE Card_Id={1}", module, card);
                var command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                Close();
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// Kill a slottable and it's child
        /// </summary>
        /// <param name="rackId">Rack Id</param>
        /// <param name="slot">Slot Position</param>
        public void DeleteSlottable(int rackId, int slot)
        {
            var slottable = GetSlottable(rackId, slot, false);
            if (slottable == null) return;
            Open();
            var table = "Card";
            switch (slottable.SlotType)
            {
                case 'N':
                    table = "NetworkCard";
                    break;
                case 'P':
                    table = "Processor";
                    break;
                case 'C':
                    table = "Card";
                    var rack = GetRack(rackId);
                    Open();
                    var card = (Card)rack.Cards[slot];
                    Open();
                    var sql = new SqlCommand("DELETE FROM Sensor WHERE Card_Id=" + card.CardId, connection);
                    sql.ExecuteNonQuery();
                    Open();
                    break;
            }
            var command = new SqlCommand("DELETE FROM " + table + " WHERE Slot_Id=" + slottable.SlotId, connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Slottable WHERE Slot_Id=" + slottable.SlotId, connection);
            command.ExecuteNonQuery();
            Close();
        }

        public List<Card> GetCards(Rack rack = null)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Card", connection);
            var cardReader = command.ExecuteReader();
            List<int[]> values = new List<int[]>();
            while (cardReader.Read())
            {
                var modId = (int)cardReader["Module_Id"];
                var cardId = (int)cardReader["Card_Id"];
                var slotId = (int)cardReader["Slot_Id"];
                values.Add(new int[]{modId,slotId,cardId});
            }
            cardReader.Close();
            List<Card> cards = new List<Card>();
            foreach (var vals in values) {
                command = new SqlCommand("SELECT * FROM Slottable WHERE Slot_Id=" + vals[1], connection);
                var reader = command.ExecuteReader();
                reader.Read();
                int rackId = (int)reader["Rack_Id"];
                int pos = (int)reader["Slot_Position"];
                reader.Close();

                command = new SqlCommand("SELECT * FROM IOModule WHERE Module_Id=" + vals[0], connection);
                var moduleReader = command.ExecuteReader();
                if (moduleReader.Read())
                {
                    var card = new Card(vals[1], vals[2], rackId, (int)moduleReader["Module_Type"], moduleReader["Module_Name"].ToString(), moduleReader["Module_Description"].ToString(), (int)moduleReader["Module_Channels"], vals[0], pos);
                    if (rack == null) cards.Add(card);
                    else
                    {
                        if (rack.Id == rackId) cards.Add(card);
                    }
                }
                moduleReader.Close();
            }
            Close();
            return cards;
        }

        public List<NetworkCard> GetNetworkCards(Rack rack = null)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM NetworkCard", connection);
            var reader = command.ExecuteReader();
            var data = new List<Object[]>();
            while (reader.Read())
            {
                data.Add(new Object[] {reader["Network_Id"], reader["Network_Model"], reader["Network_Type"], reader["Slot_Id"]});
                
            }
            reader.Close();
            
            var cards = new List<NetworkCard>();
            foreach(var d in data) {
                command = new SqlCommand("SELECT * FROM Slottable WHERE Slot_Id=" + (int)d[3], connection);
                reader = command.ExecuteReader();
                reader.Read();
                var card = new NetworkCard((int)d[0], (int)reader["Rack_Id"], d[1].ToString(), d[2].ToString()[0], (int)reader["Slot_Position"], (int)d[3]);
                if (rack == null) cards.Add(card);
                else if(rack.Id == card.RackId) cards.Add(card);
                reader.Close();
            }
            Close();
            return cards;
        }

        public List<Processor> GetProcessors(Rack rack = null)
        {
            Open();
            var command = new SqlCommand("SELECT * FROM Processor", connection);
            var reader = command.ExecuteReader();
            var data = new List<Object[]>();
            while (reader.Read())
            {
                data.Add(new Object[]{reader["Processor_Id"], reader["Processor_Model"], reader["Slot_Id"]});
            }
            reader.Close();
            var cards = new List<Processor>();
            foreach (var d in data)
            {
                command = new SqlCommand("SELECT * FROM Slottable WHERE Slot_Id=" + (int)d[2], connection);
                reader = command.ExecuteReader();
                reader.Read();
                var card = new Processor((int)d[0], (int)reader["Rack_Id"], d[1].ToString(), (int)d[2]);
                if (rack == null) cards.Add(card);
                else if (rack.Id == card.RackId) cards.Add(card);
                reader.Close();
            }
            Close();
            return cards;
        }


        /// <summary>
        /// Delete All Table Data for Inital Data Loading
        /// </summary>
        public void DeleteAllTableData()
        {
            Open();
            var command = new SqlCommand("DELETE FROM Sensor", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Card", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM IOModule", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM NetworkCard", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Processor", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Slottable", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Rack", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("DELETE FROM Room", connection);
            command.ExecuteNonQuery();
            Close();
        }

        /// <summary>
        /// Get CPM report data set from database
        /// <returns>CPM report data set</returns>
        /// </summary>
        public List<CpmReport> GetCpmReport()
        {
            List<CpmReport> cpmReportList = new List<CpmReport>();
            //string sql = "SELECT Sensor.Sensor_Tag, Sensor.Sensor_Param, Sensor.Sensor_Desc, Sensor.Sensor_Channel, Rack.Rack_Model, Rack.Rack_Address, Slottable.Slot_Position, Room.Room_Name FROM Sensor INNER JOIN Card ON Sensor.Card_Id = Card.Card_Id INNER JOIN Slottable ON Card.Slot_Id = Slottable.Slot_Id INNER JOIN Room INNER JOIN Rack ON Room.Room_Id = Rack.Room_Id ON Slottable.Rack_Id = Rack.Rack_Id";
            string sql = "SELECT Sensor.Sensor_Tag, Sensor.Sensor_Param, Sensor.Sensor_Desc, ";
            sql += "Sensor.Sensor_Channel, Rack.Rack_Model, Rack.Rack_Address, Slottable.Slot_Position, ";
            sql += "Room.Room_Name FROM Sensor INNER JOIN Card ON Sensor.Card_Id = Card.Card_Id ";
            sql += "INNER JOIN Slottable ON Card.Slot_Id = Slottable.Slot_Id INNER JOIN Room ";
            sql += "INNER JOIN Rack ON Room.Room_Id = Rack.Room_Id ON Slottable.Rack_Id = Rack.Rack_Id ";
            sql += "ORDER BY Rack.Rack_Model, Rack.Rack_Address, Slottable.Slot_Position, Sensor.Sensor_Channel";

            try
            {
                Open();
                var command = new SqlCommand(sql, connection);
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string tag = reader["Sensor_Tag"].ToString();
                    string param = reader["Sensor_Param"].ToString();
                    string desc = reader["Sensor_Desc"].ToString();
                    int channel = (int)reader["Sensor_Channel"];
                    string model = reader["Rack_Model"].ToString();
                    string address = reader["Rack_Address"].ToString();
                    int position = (int)reader["Slot_Position"];
                    string roomName = reader["Room_Name"].ToString();

                    cpmReportList.Add(new CpmReport(tag, param, desc, channel, model, address, position, roomName));
                }

                Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }
            catch (Exception e)
            {   // Handle unique constraint violation
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + e.ToString());
                Close();
            }

            return cpmReportList;
        }

        private void Open()
        {
            //Console.WriteLine("Openning Connection");
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private void Close()
        {
            //Console.WriteLine("Closing Connection");
            if (connection != null && connection.State == System.Data.ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }
}
